package bysj.dyz.dao;

import bysj.dyz.bean.User;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class UserDao {
    /**
     * 查询所有申领总条数
     * @return
     */

    public int queryCount() throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="select count(*) from user";
        Long query = queryRunner.query(sql, new ScalarHandler<>());

        return  query.intValue();

    }

    /**
     * 分页查询所有申领
     * @param startPosition
     * @param currentCount
     * @return
     */
    public List<User> pageselectuser(int startPosition, int currentCount) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="select * from user limit ?,?";
        List<User> query = queryRunner.query(sql, new BeanListHandler<User>(User.class),startPosition,currentCount);

        return  query;
    }

    public boolean deleteuser(String userid) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sqlgood="delete from good where fabuid=?";
        int update1 = queryRunner.update(sqlgood, userid);
        String sqlapply="delete from apply where applyuserid=?";
        int update2= queryRunner.update(sqlapply, userid);
        String sql="delete from user where userid=?";
        int update = queryRunner.update(sql, userid);
        if (update>0){
            return  true;
        }else {
            return  false;
        }
    }

    /**
     * 查询用户名是否存在
     * @param username
     * @return
     */
    public boolean checkUser(String username) {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="select * from user where username=?";
        try {
            User query = queryRunner.query(sql, new BeanHandler<User>(User.class), username);
            if (query==null){
                return  true;

            }else {
                return  false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return  false;
        }

    }

    /**
     * 插入注册信息
     * @param user
     * @return
     */
    public boolean register(User user) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="insert into user values(null,?,?,?,?)";

            int update = queryRunner.update(sql, user.getUsername(), user.getUserpassword(), user.getUseremail(),user.getUserphone());

            //行数大于零说明注册成功
            if (update>0){
                return  true;

            }else {
                return  false;
            }

    }
}
